﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.IO;
using System.Data.SQLite;

/*
	10/07/2012 Big refactor done.
*/

namespace SqliteDriver
{
	public class SqliteDataSource : DbViewDriver.IDataSource, IDisposable
	{
		//////////////////////////////////////////////////////////
		// Central resource.
		// 
		static DbViewDriver.IDataSource g_source = new SqliteDataSource();
		public static DbViewDriver.IDataSource Source { get { return g_source; } }

		public enum Version { UNKNOWN = 0, Three };
		
		public SqliteDataSource()
		{
			// initialise classes to handle various groups of driver functions
			// DataTable generation from SQL
			this.tableCommandProcessor = new TableCommandProcessor(this);
			// Source code generation and searching
			this.sourcecodeCommandProcessor = new SourcecodeCommandProcessor(this);
			// other functions 
			this.extensionCommandProcessor = new ExtensionCommandProcessor(this);
			
			// add specialised help 
			Cmd.ResetHelp(DbView.CommandHelpRepository.Instance);
			
		}
		
		~SqliteDataSource()
		{
			// On application shutdown you have to hope this is triggered.
		}

		public void Dispose()
		{
			// Main program should call dispose on each driver it knows about.
		}

		//////////////////////////////////////////////////////////
		// interface DbViewDriver.IDataSource
		// 
		public String ConnectionString
		{
			get { return connectionString; }
		}
		// unit testing
		public void SetConnectionString(String connectionStringParam) { this.connectionString = connectionStringParam ; }

		public bool IsDevelopmentDatabase
		{
			get { return connectionInfo.AllowChanges; }
		}

		public bool PerformDriverCheck(out String configErrorMessage)
		{
			try 
			{
				using (SQLiteConnection sqlConn = new SQLiteConnection())
				{
					configErrorMessage = "OK";
					return true;
				}
			}
			catch(Exception exc)
			{
				// not expecting this ever (ha ha)
				configErrorMessage = String.Format(
@"SQLite Driver has hit an unexpected error condition. Details are below. 
Please refer to the DbViewSharp website for details on how to fix this. If
there are none then please consider contacting the project administrator 
with details. The message reads:

{0}", exc.Message);
				return false;
			}
		}

		public void SetCurrentConnection(DbView.ConnectionInfo connectionInfo)
		{
			this.connectionInfo = connectionInfo;
			this.connectionString = MakeConnectionString(connectionInfo);
			// reset version to unk, which also retests for case-sensitivity
			this.sqlVersion = Version.UNKNOWN;
		}
		
		// Who is this driver?
		public String DriverName {	get { return "SQLite"; } }

		public bool SupportsCommand(DbView.Commands.Cmnd command)
		{
			// [Specialise: add supported commands ]
			if (command == DbView.Commands.Cmnd.Nothing) return true ; // sample to override template test
			
			if (this.sqlVersion == Version.UNKNOWN) DetermineSqlProperties();
			// default: see whether you get a template back. Return T if yes F otherwise
			String commandTemplate = SqliteTemplates.Templates.FetchTemplate(MapVersion(this.sqlVersion), command);
			return (commandTemplate.Length > 0);
		}
		// define string constants to use as special commands
		
		// build the custom main menu 
		public DbViewDriver.CustomMenuItem BuildCustomMenu()
		{
			if (this.sqlVersion == Version.UNKNOWN) DetermineSqlProperties();
			return new SqlServerPopupBuilder(IsDevelopmentDatabase).BuildCustomMenu();
		}

		// build a pop-up menu 
		public DbViewDriver.CustomMenuItem BuildPopupMenu(DbView.Command command)
		{
			return new SqlServerPopupBuilder(IsDevelopmentDatabase).BuildPopupMenu(command);
		}

		//////////////////// CommandBuilder Interface ////////
		public String MakeCommand(DbView.Command command, DbView.ViewContext context)
		{
			// get the version as it may change the SQL used
			Version databaseVersion = sqlVersion;
			if (this.sqlVersion == Version.UNKNOWN)
				databaseVersion = DetermineSqlProperties();
			// the idea for plugins is to register builders here
			// for now we'll just deal with known 
			String filterString = (this.isDbCaseSensitive) ? context.FilterString.ToUpper() : context.FilterString;
			// look up. The commented out version can fail wierdly
			String commandTemplate = SqliteTemplates.Templates.FetchTemplate(MapVersion(databaseVersion), command.Cmd);
			//String commandTemplate = SqlServerTemplates.Templates.FetchTemplate(MapVersion(this.m_SqlVersion), command.Cmd);
			if (command.Cmd == DbView.Commands.Cmnd.Extension)
				commandTemplate = SqliteTemplates.Templates.FetchExtensionTemplate(MapVersion(databaseVersion), command, command.Name);
			// if no additional parameter assume just the filter is required
			if (this.tableCommandProcessor.SimpleTemplate(commandTemplate))
			{
				return TableCommandProcessor.AddFilterClause(commandTemplate, filterString);
			}
			// [Specialise: custom sql command building ]
			switch (command.Cmd)
			{
				case DbView.Commands.Cmnd.Adhoc:
					// return filterString; // was this, but fails for case-sensitve databases.
					return context.FilterString;
				case DbView.Commands.Cmnd.Fields:
					return tableCommandProcessor.MakeFieldsCommandString(context, commandTemplate, filterString);
				case DbView.Commands.Cmnd.ViewSource:
					return SourceCommand(context);
				case DbView.Commands.Cmnd.ProcedureSource:
					return SourceCommand(context);
				case DbView.Commands.Cmnd.TableFieldSearch:
					return sourcecodeCommandProcessor.SearchSourceCommand(context);
				case DbView.Commands.Cmnd.ProcedureSourceSearch:
					return sourcecodeCommandProcessor.SearchSourceCommand(context);
				case DbView.Commands.Cmnd.AllSourceSearch:
					return sourcecodeCommandProcessor.SearchSourceCommand(context);
				case DbView.Commands.Cmnd.ViewSourceSearch:
					return sourcecodeCommandProcessor.SearchSourceCommand(context);
				case DbView.Commands.Cmnd.FunctionSourceSearch:
					return sourcecodeCommandProcessor.SearchSourceCommand(context);
				case DbView.Commands.Cmnd.TriggerSourceSearch:
					return sourcecodeCommandProcessor.SearchSourceCommand(context);
				case DbView.Commands.Cmnd.TableData:
				{
					//String tableName = TableNameFromContext(context);
					return tableCommandProcessor.TableDataCommandString(context, filterString);
				}
				case DbView.Commands.Cmnd.Aggregate:
					return tableCommandProcessor.AggregateDataCommandString(commandTemplate, context);
				case DbView.Commands.Cmnd.RowCount:
					return tableCommandProcessor.RowCountCommandString(commandTemplate, context);
				case DbView.Commands.Cmnd.MinMax:
					return tableCommandProcessor.RowCountCommandString(commandTemplate, context);
				case DbView.Commands.Cmnd.Average:
					return tableCommandProcessor.RowCountCommandString(commandTemplate, context);
				case DbView.Commands.Cmnd.LongShort:
					return tableCommandProcessor.RowCountCommandString(commandTemplate, context);
				case DbView.Commands.Cmnd.Extension:
					switch (command.Name)
					{
					}
					break;
			}
			return ""; // ie unsupported
		}

		// core Interface method to build a data table
		public DataTable BuildTable(DbView.Command command, DbView.ViewContext context)
		{
			// 
			String commandString = MakeCommand(command, context);
			if (commandString.Length == 0)
				throw new Exception(String.Format("Driver Error: {0} generated an empty command string", command));
			
			return tableCommandProcessor.BuildDataTable(command, commandString, context);
		}

		// updates the a table folowing edits
		public bool Update(DataTable table, out String statusMessage)
		{
			// need to build some sql
			//DbView.ViewDefinition viewDef = null;
			DbView.ViewDefinition viewDef = new DbView.ViewDefinition();
			viewDef.ConfigureFromTable(table);
			
			String sql = tableCommandProcessor.TableDataCommandString(table.TableName, viewDef, "");
		
			// cur from old code
			int status = 0;

			int modCount = 0;
			foreach (DataRow dr in table.Rows)
				if (dr.RowState != DataRowState.Unchanged)
					++modCount;
			statusMessage = String.Format("{0} changed rows", modCount);
			try
			{
				// do it.
				SQLiteDataAdapter myDataAdapter = new SQLiteDataAdapter();
				// the sql and the command builder automatically generate the 
				// insert/update/delete sql
				SQLiteConnection databaseConnection = new SQLiteConnection(ConnectionString);
				databaseConnection.Open();
				
				myDataAdapter.SelectCommand = new SQLiteCommand(sql, databaseConnection);
				SQLiteCommandBuilder cb = new SQLiteCommandBuilder(myDataAdapter);

				// Without the SqlCommandBuilder this line would fail
				// below apparently checks the state of each row in the table
				// and applies the correct sql to update it.
				myDataAdapter.Update(table);
				databaseConnection.Close();
				status = 1;
			}
			catch (DBConcurrencyException e)
			{
				statusMessage = e.Message;
				status = -3;
				//MessageBox.Show(message);
			}
			catch (InvalidOperationException e)
			{
				statusMessage = e.Message;
				status = -2;
			}
			catch (SQLiteException e)
			{
				statusMessage = e.Message;
				status = -1;
			}
			return (status == -1) ? false : true;
		}

		public String GetSourceCode(DbView.Command command)
		{
			// delegated.
			return sourcecodeCommandProcessor.ProcessCommand(command);
		}

		// wrong. This is search source code
		public String GetSourceCode(DbView.ViewContext context, String objectName)
		{
			return sourcecodeCommandProcessor.GetSourceCode(context, objectName);
		}

		// Delegates 
		public DbView.Command ExecuteDriverAction(DbView.Command command, object window, DbViewDriver.IConnectionConfig connectionConfig, String searchText, out bool refreshGrid)
		{
			DbView.Command nextCommand;
			refreshGrid = extensionCommandProcessor.ProcessCommand(command, window, connectionConfig, searchText, out nextCommand);
			return nextCommand;
		}

		public void GetTableSchema(String tableName, out DataTable table, bool full)
		{
			table = SqlTableSchema.MakeFieldInfoTable(this.connectionString, tableName);
		}

		// Configure driver
		public bool ConfigureConnection(DbViewDriver.IConnectionConfig connectionConfig)
		{
			// display the dialog.
			using (SqliteConnectionEditForm dlg = new SqliteConnectionEditForm(connectionConfig, this))
			{ return (dlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) ? true : false; }
		}
		
		public String GetSchemaSql(String tableName)
		{
			// get the correct template
			String sqlTemplate = SqliteTemplates.Templates.FetchTemplate(MapVersion(this.sqlVersion), DbView.Commands.Cmnd.Fields);
			// remove special where clause as the result should always include all fields.
			sqlTemplate = SqliteTemplates.Templates.ProcessFilterClause(sqlTemplate, "");
			// return template with required tablename added
			return String.Format(sqlTemplate, tableName);
		}

		public DbViewDriver.IDatabaseExportSource DatabaseExportSource 
		{
			get{ return new SqliteImportExportInterfaceImplementation(); }
			set {} // don't bother when 
		}

		public object TryToGetOptionalInterface(DbViewDriver.OptionalInterface requestedInterface)
		{
			if (requestedInterface == DbViewDriver.OptionalInterface.Export)
				return this.DatabaseExportSource;
			if (requestedInterface == DbViewDriver.OptionalInterface.Schema ||
				requestedInterface == DbViewDriver.OptionalInterface.Lookup)
				return new SqliteOptionInterfaceImplementations(this);
			return null;
		}


		public static String MakeConnectionStringEx(DbView.ConnectionInfo connectionInfo)
		{
			String fileName = Path.Combine(connectionInfo.Server, connectionInfo.Database);
			String connstr = String.Format("data source={0}", fileName);
			if (!string.IsNullOrEmpty(connectionInfo.PWD))
				connstr += string.Format("; Password={0}", connectionInfo.PWD);
			return connstr;
		}
		
		// Original interface: used for connecting properly
		public static String MakeConnectionString(DbView.ConnectionInfo connectionInfo)
		{
			return MakeConnectionStringEx(connectionInfo);
		}

		//public static DbView.ConnectionInfo InfoFromConnectionString(string connectionString)
		//{
		//    // This is SQL server version. 
		//    DbView.ConnectionInfo ci = new DbView.ConnectionInfo();
		//    ci.Database = ExtractConnectionStringParam(connectionString, "initial catalog");
		//    ci.Server = ExtractConnectionStringParam(connectionString, "data source");
		//    ci.UID = ExtractConnectionStringParam(connectionString, "user id");
		//    ci.PWD = ExtractConnectionStringParam(connectionString, "password");
		//    return ci;
		//}

		//static String ExtractConnectionStringParam(String connectionString, String param)
		//{
		//    int start = connectionString.ToUpper().IndexOf(param.ToUpper());
		//    if (start == -1) return "";
		//    start += param.Length + 1;
		//    int end = connectionString.IndexOf(";", start);
		//    if (end == -1) end = connectionString.Length;
		//    return connectionString.Substring(start, end - start);
		//}

		//// Static helper for other classes in the driver
		//public static String BuildConnectionString(DbView.ConnectionInfo connectionInfo)
		//{
		//    String fileName = Path.Combine(connectionInfo.Server, connectionInfo.Database);
		//    String connStr = String.Format("Data Source={0}", fileName);
		//    return connStr;
		//}

 		public static bool IsValid(DbView.ConnectionInfo connectionInfo)
		{
			// all commections need a name
			if (connectionInfo.Name.Length == 0) return false;
			// others need a database and server
			if (connectionInfo.Server.Length == 0 || connectionInfo.Database.Length == 0)
				return false;
			// It may be OK without user and pwd as it will switch to 
			// trusted connection
			return true;
		}

		// exposed access for helper (processor classes)
		// internal bool IsCaseSensitive {get { return isDbCaseSensitive; } }
		internal int SqlVersion {get { return this.MapVersion(this.sqlVersion); } }
		
		internal void ResetPassword(string previousConnstr, string newConnstr)
		{
			System.Console.WriteLine("PASSWORD CHANGE {0} to {1}", previousConnstr, newConnstr);

		}
		//////////////////// helpers ////////
		internal SqlTableSchema MakeSchemaFor(String tableName)
		{
			return Helper.MakeSchemaFor(tableName, connectionString);
		}

		String SourceCommand(DbView.ViewContext context)
		{
			String objectName = ObjectNameFromContext(context);
			return objectName; // see GetSource below. Only requires the name
		}

		String ObjectNameFromContext(DbView.ViewContext context)
		{
			// works for views, tables, Procs.
			return context.ObjectName;
		}
		
		// The version may decide the sql template to use.
		Version DetermineSqlProperties()
		{
			this.sqlVersion = Version.Three;
			return this.sqlVersion;
		}

		int MapVersion(Version sqlVersion)
		{
			return (sqlVersion == Version.Three) ? 3 : -1;
		}

		/////////////////////////////////////////////////////////////////
		// private
		String connectionString;
		DbView.ConnectionInfo connectionInfo;
		// requires state (curse it).
		ExtensionCommandProcessor extensionCommandProcessor;
		TableCommandProcessor tableCommandProcessor;
		SourcecodeCommandProcessor sourcecodeCommandProcessor;
		
		Version sqlVersion = Version.UNKNOWN;   // SQl server version 
		bool isDbCaseSensitive = false;


	}
}
